Prosper Load Data Analysis
by Rehab Fathi Ali
Introduction
Prosper is America’s first peer-to-peer lending marketplace, with more than 2 million members and over $2,000,000,000 in funded loans. Here we use the data available to the public (last updated on March 11th, 2014) from Prosper, which contains all the listings and loans ever created with 81 variables on each loan/listing, to do some data analysis. As a potential amateur investor, I will explore the borrower market (including the demographic segmentation and beyond) and try to let data tell some ‘behind scene’ stories about the borrowers, also the performance of Prosper in terms of the volume of listings by year and by area.
Univariate Plots Section
First of all, let’s explore the data set and the variables
## 'data.frame': 113937 obs. of 81 variables:
## $ ListingKey : Factor w/ 113066 levels "00003546482094282EF90E5",..: 7180 7193 6647 6669 6686 6689 6699 6706 6687 6687 ...
## $ ListingNumber : int 193129 1209647 81716 658116 909464 1074836 750899 768193 1023355 1023355 ...
## $ ListingCreationDate : Factor w/ 113064 levels "2005-11-09 20:44:28.847000000",..: 14184 111894 6429 64760 85967 100310 72556 74019 97834 97834 ...
## $ CreditGrade : Factor w/ 9 levels "","A","AA","B",..: 5 1 8 1 1 1 1 1 1 1 ...
## $ Term : int 36 36 36 36 36 60 36 36 36 36 ...
## $ LoanStatus : Factor w/ 12 levels "Cancelled","Chargedoff",..: 3 4 3 4 4 4 4 4 4 4 ...
## $ ClosedDate : Factor w/ 2803 levels "","2005-11-25 00:00:00",..: 1138 1 1263 1 1 1 1 1 1 1 ...
## $ BorrowerAPR : num 0.165 0.12 0.283 0.125 0.246 ...
## $ BorrowerRate : num 0.158 0.092 0.275 0.0974 0.2085 ...
## $ LenderYield : num 0.138 0.082 0.24 0.0874 0.1985 ...
## $ EstimatedEffectiveYield : num NA 0.0796 NA 0.0849 0.1832 ...
## $ EstimatedLoss : num NA 0.0249 NA 0.0249 0.0925 ...
## $ EstimatedReturn : num NA 0.0547 NA 0.06 0.0907 ...
## $ ProsperRating..numeric. : int NA 6 NA 6 3 5 2 4 7 7 ...
## $ ProsperRating..Alpha. : Factor w/ 8 levels "","A","AA","B",..: 1 2 1 2 6 4 7 5 3 3 ...
## $ ProsperScore : num NA 7 NA 9 4 10 2 4 9 11 ...
## $ ListingCategory..numeric. : int 0 2 0 16 2 1 1 2 7 7 ...
## $ BorrowerState : Factor w/ 52 levels "","AK","AL","AR",..: 7 7 12 12 25 34 18 6 16 16 ...
## $ Occupation : Factor w/ 68 levels "","Accountant/CPA",..: 37 43 37 52 21 43 50 29 24 24 ...
## $ EmploymentStatus : Factor w/ 9 levels "","Employed",..: 9 2 4 2 2 2 2 2 2 2 ...
## $ EmploymentStatusDuration : int 2 44 NA 113 44 82 172 103 269 269 ...
## $ IsBorrowerHomeowner : Factor w/ 2 levels "False","True": 2 1 1 2 2 2 1 1 2 2 ...
## $ CurrentlyInGroup : Factor w/ 2 levels "False","True": 2 1 2 1 1 1 1 1 1 1 ...
## $ GroupKey : Factor w/ 707 levels "","00343376901312423168731",..: 1 1 335 1 1 1 1 1 1 1 ...
## $ DateCreditPulled : Factor w/ 112992 levels "2005-11-09 00:30:04.487000000",..: 14347 111883 6446 64724 85857 100382 72500 73937 97888 97888 ...
## $ CreditScoreRangeLower : int 640 680 480 800 680 740 680 700 820 820 ...
## $ CreditScoreRangeUpper : int 659 699 499 819 699 759 699 719 839 839 ...
## $ FirstRecordedCreditLine : Factor w/ 11586 levels "","1947-08-24 00:00:00",..: 8639 6617 8927 2247 9498 497 8265 7685 5543 5543 ...
## $ CurrentCreditLines : int 5 14 NA 5 19 21 10 6 17 17 ...
## $ OpenCreditLines : int 4 14 NA 5 19 17 7 6 16 16 ...
## $ TotalCreditLinespast7years : int 12 29 3 29 49 49 20 10 32 32 ...
## $ OpenRevolvingAccounts : int 1 13 0 7 6 13 6 5 12 12 ...
## $ OpenRevolvingMonthlyPayment : num 24 389 0 115 220 1410 214 101 219 219 ...
## $ InquiriesLast6Months : int 3 3 0 0 1 0 0 3 1 1 ...
## $ TotalInquiries : num 3 5 1 1 9 2 0 16 6 6 ...
## $ CurrentDelinquencies : int 2 0 1 4 0 0 0 0 0 0 ...
## $ AmountDelinquent : num 472 0 NA 10056 0 ...
## $ DelinquenciesLast7Years : int 4 0 0 14 0 0 0 0 0 0 ...
## $ PublicRecordsLast10Years : int 0 1 0 0 0 0 0 1 0 0 ...
## $ PublicRecordsLast12Months : int 0 0 NA 0 0 0 0 0 0 0 ...
## $ RevolvingCreditBalance : num 0 3989 NA 1444 6193 ...
## $ BankcardUtilization : num 0 0.21 NA 0.04 0.81 0.39 0.72 0.13 0.11 0.11 ...
## $ AvailableBankcardCredit : num 1500 10266 NA 30754 695 ...
## $ TotalTrades : num 11 29 NA 26 39 47 16 10 29 29 ...
## $ TradesNeverDelinquent..percentage. : num 0.81 1 NA 0.76 0.95 1 0.68 0.8 1 1 ...
## $ TradesOpenedLast6Months : num 0 2 NA 0 2 0 0 0 1 1 ...
## $ DebtToIncomeRatio : num 0.17 0.18 0.06 0.15 0.26 0.36 0.27 0.24 0.25 0.25 ...
## $ IncomeRange : Factor w/ 8 levels "$0","$1-24,999",..: 4 5 7 4 3 3 4 4 4 4 ...
## $ IncomeVerifiable : Factor w/ 2 levels "False","True": 2 2 2 2 2 2 2 2 2 2 ...
## $ StatedMonthlyIncome : num 3083 6125 2083 2875 9583 ...
## $ LoanKey : Factor w/ 113066 levels "00003683605746079487FF7",..: 100337 69837 46303 70776 71387 86505 91250 5425 908 908 ...
## $ TotalProsperLoans : int NA NA NA NA 1 NA NA NA NA NA ...
## $ TotalProsperPaymentsBilled : int NA NA NA NA 11 NA NA NA NA NA ...
## $ OnTimeProsperPayments : int NA NA NA NA 11 NA NA NA NA NA ...
## $ ProsperPaymentsLessThanOneMonthLate: int NA NA NA NA 0 NA NA NA NA NA ...
## $ ProsperPaymentsOneMonthPlusLate : int NA NA NA NA 0 NA NA NA NA NA ...
## $ ProsperPrincipalBorrowed : num NA NA NA NA 11000 NA NA NA NA NA ...
## $ ProsperPrincipalOutstanding : num NA NA NA NA 9948 ...
## $ ScorexChangeAtTimeOfListing : int NA NA NA NA NA NA NA NA NA NA ...
## $ LoanCurrentDaysDelinquent : int 0 0 0 0 0 0 0 0 0 0 ...
## $ LoanFirstDefaultedCycleNumber : int NA NA NA NA NA NA NA NA NA NA ...
## $ LoanMonthsSinceOrigination : int 78 0 86 16 6 3 11 10 3 3 ...
## $ LoanNumber : int 19141 134815 6466 77296 102670 123257 88353 90051 121268 121268 ...
## $ LoanOriginalAmount : int 9425 10000 3001 10000 15000 15000 3000 10000 10000 10000 ...
## $ LoanOriginationDate : Factor w/ 1873 levels "2005-11-15 00:00:00",..: 426 1866 260 1535 1757 1821 1649 1666 1813 1813 ...
## $ LoanOriginationQuarter : Factor w/ 33 levels "Q1 2006","Q1 2007",..: 18 8 2 32 24 33 16 16 33 33 ...
## $ MemberKey : Factor w/ 90831 levels "00003397697413387CAF966",..: 11071 10302 33781 54939 19465 48037 60448 40951 26129 26129 ...
## $ MonthlyLoanPayment : num 330 319 123 321 564 ...
## $ LP_CustomerPayments : num 11396 0 4187 5143 2820 ...
## $ LP_CustomerPrincipalPayments : num 9425 0 3001 4091 1563 ...
## $ LP_InterestandFees : num 1971 0 1186 1052 1257 ...
## $ LP_ServiceFees : num -133.2 0 -24.2 -108 -60.3 ...
## $ LP_CollectionFees : num 0 0 0 0 0 0 0 0 0 0 ...
## $ LP_GrossPrincipalLoss : num 0 0 0 0 0 0 0 0 0 0 ...
## $ LP_NetPrincipalLoss : num 0 0 0 0 0 0 0 0 0 0 ...
## $ LP_NonPrincipalRecoverypayments : num 0 0 0 0 0 0 0 0 0 0 ...
## $ PercentFunded : num 1 1 1 1 1 1 1 1 1 1 ...
## $ Recommendations : int 0 0 0 0 0 0 0 0 0 0 ...
## $ InvestmentFromFriendsCount : int 0 0 0 0 0 0 0 0 0 0 ...
## $ InvestmentFromFriendsAmount : num 0 0 0 0 0 0 0 0 0 0 ...
## $ Investors : int 258 1 41 158 20 1 1 1 1 1 ...Explore the annual income of the borrowers
- Some loans were given to borrowers who are unemployed or have $0 income
- The majority of the loans were given to citizens whose income is $25,000 - $75,000
- Some values are missing for the income range
Loans distributed by year
- The number of loans decreased dramatically in 2009
- By doing GooGle searh to investigate this derease, it turned out that this period was the financial crisis and government must have conducted certain rules on applying for loans.
The purpose of loans
- The majority of loans were for “Debt Consolidation”. Borrowers didn’t specify the real purpose which is the origin of the debt. The debt could be to but a house, car…etc
Loan status term
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 12.00 36.00 36.00 40.83 36.00 60.00
- Most borrowers choose a loan term of 36 months or 3 years
- Just few borrowers choose short loans of one year
The loan amount
- The loan amount spikes at whole numbers ($5,000, $10,000, $15,000) which is convenient to borrowers to borrow whole amounts
- Some loans have surprisingly low amounts
- The majority of loans are below $20,000
- The loan amounts is a very important feature and we should do some statistics
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 1000 4000 6500 8337 12000 35000Number of investors per loan
- Most loans have 1 investor which is understandable due to the high percentage of small loans.
Employment duration of borrower
- Borrowers tend to have smaller number of employments years ( younger citizens)
- When the citizen gets older (work more years), they have lower chance of needing a loan
Debt-to-income ratio
- Most borrowers have debt-to income ratio < 0.5. So let’s investigate more the statistics
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.000 0.140 0.220 0.276 0.320 10.010 8554Univariate Analysis
What is the structure of your dataset?
This data set contains 113,937 loans with 81 variables on each loan, including loan amount, borrower rate (or interest rate), current loan status, borrower income, borrower employment status, borrower credit history, and the latest payment information.
What is/are the main feature(s) of interest in your dataset?
- Term: The length of the loan expressed in months.
- LoanStatus: The current status of the loan.
- ListingCategory: The category of the listing that the borrower selected when posting their listing.
- CreditScoreRangeLower: The lower value representing the range of the borrower’s credit score as provided by a consumer credit rating agency.
- CreditScoreRangeUpper: The upper value representing the range of the borrower’s credit score as provided by a consumer credit rating agency.
- BankcardUtilization: The percentage of available revolving credit that is utilized at the time the credit profile was pulled.
- IncomeRange: The income range of the borrower at the time the listing was created.
- LoanOriginalAmount: The origination amount of the loan.
- Investors: The number of investors that funded the loan.
What other features in the dataset do you think will help support your investigation into your feature(s) of interest?
- ListingCreationDate: The date the listing was created.
- IsBorrowerHomeowner: A Borrower will be classified as a homowner if they have a mortgage on their credit profile or provide documentation confirming they are a homeowner.
- BorrowerAPR: The Borrower’s Annual Percentage Rate (APR) for the loan.
- BorrowerRate: The Borrower’s interest rate for this loan.
- Recommendations: Number of recommendations the borrower had at the time the listing was created.
- TotalProsperLoans: Number of Prosper loans the borrower at the time they created this listing. This value will be null if the borrower had no prior loans.
- DebtToIncomeRatio: The debt to income ratio of the borrower at the time the credit profile was pulled.
- StatedMonthlyIncome: The monthly income the borrower stated at the time the listing was created.
Did you create any new variables from existing variables in the dataset?
I factorized some variables and created a new variable for the loan creation date.
Of the features you investigated, were there any unusual distributions? Did you perform any operations on the data to tidy, adjust, or change the form of the data? If so, why did you do this?
Yes, I was surprised to see that some borrowers were approved to get loans despite being unemployed or have $0 income. A possible cause is that these borrowers have other sources of income. Also the loan amount had spikes at whole loan amounts. I adjusted the plot to see these spikes and explore the loan amounts.
Bivariate Plots Section
Loan status per prosper rate
- The chart shows that the higher the rating, the more likely the loan is deafulted or past due.
- An interesting observation is that loans with no prosper rating have the highest defaulted perentage. The prosper data was applied after 2009, so these loans could have been in the financial crisis period.
What are the factors affecting the loan amount ?
Employment status and loan amount
- The employed borrowers have the highes loan amount median.
- Part-time employees tend to apply for smaller loans.
Loan Purpose and Loan Amount
- The categories plot shows more details than the previous plot where the majority loans were for debt consolidation.
- Wedding loans, baby&adoption and business loans stand out among other categories.
- The borrower rate doesn’t vary much among all categories
Loan Term and Loan amount
- The output is logical and was expected. Larger loans require more time to repay so the loan term increase with loan amount.
- We previously found that the majority of borrowers chose a 36 months term. So we could investigate more the loan amount/loan term statistics.
Number of investors and loan amount
- There is a high corelation between the loan amount and number of investors. Larger loans have more investors
Let’s investigate more about the debt-to-income ratio
Monthly income and debt-to-income ratio
- The debt-to-inome ratio decreases as the monthly income increases
- Most borrowers have income <$10,000 and debt-to-income ratio < 0.5
Borrower rate and Debt-to-income ration
- There is no correlation between the interest rate and the debt ratio
- We would expect that borrowers with high debts will pay higher interest rates and make bigger loans but the plot proved this assumption is wrong
Bivariate Analysis
Talk about some of the relationships you observed in this part of the investigation. How did the feature(s) of interest vary with other features in the dataset?
The obvious observation is the strong correlation between the loan term and the loan amount. Also the negative correlation between the monthly income and the debt ratio. Other investigations did not prove correlation between features.
Did you observe any interesting relationships between the other features (not the main feature(s) of interest)?
No, I didn’t.
What was the strongest relationship you found?
The strongest relationship is the one between the loan amount and the loan term which was predictable.
Multivariate Plots Section
The purpose of investigating loans is usually to determine if the borrower will be able to repay the loan. So let’s first identify borrowers who wre unable to return the loans. The laws state that a borrower is delinquent if he is past due more than 90 days
Delinquency with Credit score and Loan amount
There are some interest findings in the plot - Delinquent users tend to have lower credit score and tend to make smaller loans - Good borrowers tend to make bigger loans and have better redit scores
Delinquency with loan amount and interest rate
- Smaller loans have higher interest rate
- Delinquent borrowers tend to have smaller loans like the previous plots and this plot shows a possible reason, as these loans have higher interest rate.
Delinquency with credit score and interest rate
- This plot shows a high corelation. The majority of delinquent borrowers have low credit score and their loans have high interest rate
Delinquency with Monthly Income and Debt-to-Income Ratio
- Delinquent borrowers tend to have lower monthly income and higher debt-to-income ratios
- Although the debt-to-income ratio decreases as the income increase, there is still a chance of delinquency.
- One other finding is that most borrowers have monthly income <$15,000 and debt-to-income ratio < 0.5
Borrowers across listing categories and Delinquency
- We can examine the loans delinqueny across all categories
- The category “RV” has no delinquent borrowers but this could be due to the small number of loans in this category
- Most loans were for purpose of debt consolidation as borrowers tend not to specify the exact loan purpose
- Baby&adoption and weddings come next to debt consolidation
Multivariate Analysis
Talk about some of the relationships you observed in this part of the investigation. Were there features that strengthened each other in terms of looking at your feature(s) of interest?
A relationship that was reveeled in most plots is that delinquent borrowers usually had loans with smaller amounts which had higher interest rates Also delinquent borrowers tend to have bad credit score
Were there any interesting or surprising interactions between features?
Yes, I was surprised to see that smaller loans had higher interest rates. I thought that higher interest rates were applied to bigger loans.
Final Plots and Summary
Plot One
Description One
Despite being simple, this plot is the most important one in my opinion. It shows an important period in American finance ( The financial crisis). The number of loans decreased significantly in 2009 just after the financial crisis and increased gradually in the following years.
Plot Two
Description Two
The main purpose of investigating loans datasets is usually to identify risks and spot borrowers who are more likely to fail to repay the loans. This plot shows a strong relationship between monthly income, debt-to-income ration and deilnquency. - Delinquent borrowers tend to have lower monthly income and higher debt-to-income ratios - Although the debt-to-income ratio decreases as the income increase, there is still a chance of delinquency. - One other finding is that most borrowers have monthly income <$15,000 and debt-to-income ratio < 0.5
Plot Three
Description Three
An important feature in the dataset is the purpose of the loan. Most loans were for purpose of debt consolidation as borrowers tend not to specify the exact loan purpose. Baby&adoption and weddings come next to debt consolidation.
Reflection
I chose this dataset after watching the Big Short movie. The features in this data are very interesting.
The investigations proved common sense and logial relationships like the tendency of citizens with low credit score to become delinquent and the corelation between interest rate and delinquency.
One of the difficulties I faced was the turning point of 2009 ( After the financial crisis). Many features were only applicable after 2009 which affected the output. Also there are too many variables so many relationships were still undiscovered.
Some recommendations to get more data from this dataset is to add more important features like sex, age and citizen class ( which could be computed from monthly income and state/ area average income).